﻿using System;
using System.Configuration;
using System.Data;
using System.Text;
using System.Web.UI;
using System.Collections;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

using Microsoft.Reporting.WebForms;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace dcwj
{
    public partial class jsdbwzPage : System.Web.UI.Page
    {
        private String M_CONSTR = ConfigurationManager.ConnectionStrings["dcwjConnectionString"].ToString();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                try
                {
                    //PaperList pl = (PaperList)Context.Handler;
                    ArrayList ar = Session["selList"] as ArrayList;
           
                    if (ar == null) Response.Redirect("");
                    Database objDB = new SqlDatabase(M_CONSTR);
                    
                    DataSet dsAllClass = new DataSet();
                    DataSet dsAllClass1 = new DataSet();
                    DataSet dsAllClass2 = new DataSet();
                    DataSet dsAllClass3 = new DataSet();
                    DataSet dsAllClass4 = new DataSet();
                    DataSet dsTeacherClass = new DataSet();
                 

                    for (int i = 0; i < ar.Count; i++)
                    {
                        DataSet dsClass = objDB.ExecuteDataSet(CommandType.Text, @"select             
b.teachername,
a.score
from
(SELECT avg(cast (QAnswer as decimal)) as score FROM Table_RESULT where paperid =" + ar[i].ToString() + @" and qid<18 ) as a,
(SELECT teachername FROM Table_TEACHER where teacherid=
(select teacherid from table_paper where paperid=" + ar[i].ToString() + @" )) as b");

                        dsAllClass.Merge(dsClass);



                        DataSet dsClass1 = objDB.ExecuteDataSet(CommandType.Text, @"select             
b.teachername,
a.score
from
(SELECT avg(cast (QAnswer as decimal)) as score FROM Table_RESULT where paperid =" + ar[i].ToString() + @" and qid in(1,2,3,4,5) ) as a,
(SELECT teachername FROM Table_TEACHER where teacherid=
(select teacherid from table_paper where paperid=" + ar[i].ToString() + @" )) as b");

                        dsAllClass1.Merge(dsClass1);

                        DataSet dsClass2 = objDB.ExecuteDataSet(CommandType.Text, @"select             
b.teachername,
a.score
from
(SELECT avg(cast (QAnswer as decimal)) as score FROM Table_RESULT where paperid =" + ar[i].ToString() + @" and qid in(6,7,8) ) as a,
(SELECT teachername FROM Table_TEACHER where teacherid=
(select teacherid from table_paper where paperid=" + ar[i].ToString() + @" )) as b");

                        dsAllClass2.Merge(dsClass2);


                        DataSet dsClass3 = objDB.ExecuteDataSet(CommandType.Text, @"select             
b.teachername,
a.score
from
(SELECT avg(cast (QAnswer as decimal)) as score FROM Table_RESULT where paperid =" + ar[i].ToString() + @" and qid in(9,10,11,12,13) ) as a,
(SELECT teachername FROM Table_TEACHER where teacherid=
(select teacherid from table_paper where paperid=" + ar[i].ToString() + @" )) as b");

                        dsAllClass3.Merge(dsClass3);


                        DataSet dsClass4 = objDB.ExecuteDataSet(CommandType.Text, @"select             
b.teachername,
a.score
from
(SELECT avg(cast (QAnswer as decimal)) as score FROM Table_RESULT where paperid =" + ar[i].ToString() + @" and qid in(14,15,16,17) ) as a,
(SELECT teachername FROM Table_TEACHER where teacherid=
(select teacherid from table_paper where paperid=" + ar[i].ToString() + @" )) as b");

                        dsAllClass4.Merge(dsClass4);


                        
                    }


                    jsdbwzDS ds = new jsdbwzDS();

                    for (int j = 0; j < ar.Count; j++)
                    {
                        DataSet dsTmp = objDB.ExecuteDataSet(CommandType.Text, @"select 
t.TeacherName,
p.PaperName from Table_PAPER p, Table_TEACHER t
where p.PaperID="+ar[j].ToString()+" and p.teacherid = t.teacherid");
                        ds.TeacherClass.AddTeacherClassRow(dsTmp.Tables[0].Rows[0][0].ToString(),
                            dsTmp.Tables[0].Rows[0][1].ToString());
                    }
                    if (dsAllClass != null && dsAllClass.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass.Tables[0].Rows.Count; i++)
                        {
                            ds.TeacherAll.AddTeacherAllRow(
                                dsAllClass.Tables[0].Rows[i][0].ToString(),
                                dsAllClass.Tables[0].Rows[i][1].ToString());
                        }
                    }

                    if (dsAllClass1 != null && dsAllClass1.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass1.Tables[0].Rows.Count; i++)
                        {
                            ds.Teacher1.AddTeacher1Row(
                                 dsAllClass1.Tables[0].Rows[i][0].ToString(),
                                 dsAllClass1.Tables[0].Rows[i][1].ToString());
                        }
                    }

                    if (dsAllClass2 != null && dsAllClass2.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass2.Tables[0].Rows.Count; i++)
                        {
                            ds.Teacher2.AddTeacher2Row(
                                  dsAllClass2.Tables[0].Rows[i][0].ToString(),
                                  dsAllClass2.Tables[0].Rows[i][1].ToString());
                        }
                    }

                    if (dsAllClass3 != null && dsAllClass3.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass3.Tables[0].Rows.Count; i++)
                        {
                            ds.Teacher3.AddTeacher3Row(
                                 dsAllClass3.Tables[0].Rows[i][0].ToString(),
                                 dsAllClass3.Tables[0].Rows[i][1].ToString());
                        }
                    }

                    if (dsAllClass4 != null && dsAllClass4.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < dsAllClass4.Tables[0].Rows.Count; i++)
                        {
                            ds.Teacher4.AddTeacher4Row(
                                   dsAllClass4.Tables[0].Rows[i][0].ToString(),
                                   dsAllClass4.Tables[0].Rows[i][1].ToString());
                        }
                    }

                    ReportDataSource rds0 = new ReportDataSource("TeacherAll", ds.TeacherAll.Select("", "得分 desc"));
                    ReportDataSource rds1 = new ReportDataSource("Teacher1", ds.Teacher1.Select("", "得分 desc"));
                    ReportDataSource rds2 = new ReportDataSource("Teacher2", ds.Teacher2.Select("", "得分 desc"));
                    ReportDataSource rds3 = new ReportDataSource("Teacher3", ds.Teacher3.Select("", "得分 desc"));
                    ReportDataSource rds4 = new ReportDataSource("Teacher4", ds.Teacher4.Select("", "得分 desc"));
                    ReportDataSource rds5 = new ReportDataSource("TeacherClass", (DataTable)ds.TeacherClass);

                    ReportViewer1.LocalReport.DataSources.Add(rds0);
                    ReportViewer1.LocalReport.DataSources.Add(rds1);
                    ReportViewer1.LocalReport.DataSources.Add(rds2);
                    ReportViewer1.LocalReport.DataSources.Add(rds3);
                    ReportViewer1.LocalReport.DataSources.Add(rds4);
                    ReportViewer1.LocalReport.DataSources.Add(rds5);

                    ReportViewer1.LocalReport.Refresh();

                    Session["selList"] = null;
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                }
            }
        }
    }
}